create external table if not exists jms_ods.spm_cash_bill_detail(
     id                          bigint           comment'主键id'
    ,bill_no                     string           comment'现结账单流水号'
    ,cash_no                     string           comment'收款记录流水号'
    ,waybill_id                  bigint           comment'运单id'
    ,waybill_no                  string           comment'运单编号'
    ,type                        bigint           comment'账单类型(1:寄件,2:派件)'
    ,op_date                     date             comment'操作日期(寄件时为寄件日期,派件时为揽收日期)'
    ,p_votes                     bigint           comment'寄件票数'
    ,d_votes                     bigint           comment'派件票数'
    ,delivery_time               date             comment'寄件日期'
    ,input_time                  date             comment'运单录入日期'
    ,network_id                  bigint           comment'操作网点id'
    ,network_code                string           comment'操作网点code'
    ,network_name                string           comment'操作网点名称'
    ,staff_id                    bigint           comment'操作业务员id'
    ,staff_code                  string           comment'操作业务员code'
    ,staff_name                  string           comment'操作业务员'
    ,customer_id                 bigint           comment'客户id'
    ,customer_code               string           comment'客户编号code'
    ,customer_name               string           comment'客户编号名称'
    ,package_number              bigint           comment'件数'
    ,settlement_id               bigint           comment'结算方式id'
    ,settlement_code             string           comment'结算方式code'
    ,settlement_name             string           comment'结算方式名称'
    ,express_type_id             bigint           comment'产品类型id'
    ,express_type_code           string           comment'产品类型code'
    ,express_type_name           string           comment'产品类型名称'
    ,goods_type_id               bigint           comment'物品类型id'
    ,goods_type_code             string           comment'物品类型code'
    ,goods_type_name             string           comment'物品类型名称'
    ,dispatch_code               string           comment'派件方式code'
    ,dispatch_name               string           comment'派件方式名称'
    ,paid_mode_id                bigint           comment'付款方式/支付类型id(非运单支付类型)'
    ,paid_mode_code              string           comment'付款方式code(非运单支付类型)'
    ,paid_mode_name              string           comment'付款方式名称(非运单支付类型)'
    ,origin_id                   bigint           comment'始发地id'
    ,origin_code                 string           comment'始发地code'
    ,origin_name                 string           comment'始发地名称'
    ,destination_id              bigint           comment'目的地id'
    ,destination_code            string           comment'目的地code'
    ,destination_name            string           comment'目的地名称'
    ,package_total_weight        decimal(16,2)    comment'运单重量'
    ,s_weight                    decimal(16,2)    comment'结算重量'
    ,is_sign                     bigint           comment'签收类型0:未签收,1:正常签收2:退件签收'
    ,sign_time                   DATE             comment'签收时间'
    ,freight                     decimal(16,2)    comment'运费'
    ,package_cost                decimal(16,2)    comment'包材费'
    ,total_freight               decimal(16,2)    comment'总运费'
    ,after_tax_freight           decimal(16,2)    comment'税后总运费'
    ,tax                         decimal(16,2)    comment'预付费'
    ,handicraft_fee              decimal(16,2)    comment'手工费'
    ,other_fee                   decimal(16,2)    comment'其他费'
    ,insured_amount              decimal(16,2)    comment'保价金额'
    ,coupon_amount               decimal(16,2)    comment'优惠金额'
    ,receive_pay_fee             decimal(16,2)    comment'到付款'
    ,insured_fee                 decimal(16,2)    comment'保价费'
    ,insured                     bigint           comment'保价标识(1是，0否)'
    ,is_cod                      bigint           comment'是否需要代收货款,1是，0否'
    ,cod_money                   decimal(16,2)    comment'代收货款金额'
    ,cod_fee                     decimal(16,2)    comment'代收货款手续费'
    ,pick_finance_id             bigint           comment'寄件财务中心id'
    ,pick_finance_code           string           comment'寄件财务中心code'
    ,pick_finance_name           string           comment'寄件财务中心名称'
    ,dispatch_finance_id         bigint           comment'派件财务中心id'
    ,dispatch_finance_code       string           comment'派件财务中心code'
    ,dispatch_finance_name       string           comment'派件财务中心名称'
    ,is_void                     bigint           comment'是否作废件,1是，0否'
    ,receive_status              bigint           comment'收款状态(1:已收款2:未收款10:待支付-锁定)'
    ,receive_time                date             comment'收款时间'
    ,receiver_id                 bigint           comment'收款人id'
    ,receiver_code               string           comment'收款人code'
    ,receiver_name               string           comment'收款人姓名'
    ,create_by                   bigint           comment'创建人id'
    ,update_by                   bigint           comment'修改人id'
    ,create_by_name              string           comment'创建人姓名'
    ,update_by_name              string           comment'修改人姓名'
    ,create_time                 date             comment'创建时间'
    ,update_time                 date             comment'修改时间'
    ,trace_id                    string           comment'traceid'
    ,version                     bigint           comment'版本号'
    ,receiver_province_id        bigint           comment'收件省份id'
    ,receiver_province_name      string           comment'收件省份名称'
    ,receiver_city_id            bigint           comment'收件城市id'
    ,receiver_city_name          string           comment'收件城市名称'
    ,receipt_fee                 decimal(16,2)    comment'回单费'
    ,current_time_mills          string           comment'运单修改时间戳,用于处理mq消息先后顺序'
    ,staff_bill_type             bigint           comment'员工账单类型,1:底价结算账单,2:非底价结算'
    ,contract_area_code          string           comment'承包区code'
    ,contract_area_name          string           comment'承包区name'
)comment '网点经营-现结账单'
partitioned by (dt string comment '分区日期')
stored as parquet
location '/dw/hive/jms_ods.db/external/spm_cash_bill_detail'
tblproperties (
'discover.partitions'='false',
'parquet.column.index.access'='true'
);
